package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.CodeVO;

public class CodeDAO extends AbDao {
	private static CodeDAO dao = new CodeDAO();
	public static CodeDAO getInstance(){
		return dao;
	}
	
	public ArrayList<CodeVO> getCodeList(String groupNm){
		ArrayList<CodeVO> list = new ArrayList<CodeVO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			String bQuery = "select cd_group_no from tb_code_group where cd_group_nm like ?";
			ps = conn.prepareStatement(bQuery);
			ps.setString(1, "%"+groupNm+"%");
			rs = ps.executeQuery();
			String cdGroupNo = "";

			while(rs.next()){
				cdGroupNo = rs.getString(1);
				break;
			}
			
			rs.close();
			ps.close();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT cd_no, cd_nm, cd_desc, cd_group_no ");
			query.append("FROM tb_code ");
			query.append("WHERE cd_group_no = ?");
			ps = conn.prepareStatement(query.toString());
			ps.setString(1, cdGroupNo);
			rs = ps.executeQuery();
			while(rs.next()){
				CodeVO cvo = new CodeVO();
				cvo.setCdNo(rs.getString("cd_no"));
				cvo.setCdNm(rs.getString("cd_nm"));
				cvo.setCdDesc(rs.getString("cd_desc"));
				cvo.setCdGroupNo(rs.getString("cd_group_no"));
				list.add(cvo);
			}

			close(conn, ps, rs);
			
		}catch(Exception ex){
			System.out.println("getCodeList exception : " + ex);
			close(conn, ps, rs);
		} 
		
		return list;
	}
	
	public CodeVO getCodeVO(String cd_no){
		
		CodeVO cvo = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT cd_no, cd_nm, cd_desc, cd_group_no ");
			query.append("FROM tb_code ");
			query.append("WHERE cd_no = ? ");
			ps = conn.prepareStatement(query.toString());
			ps.setString(1, cd_no);
			rs = ps.executeQuery();
			while(rs.next()){
				System.out.println("code exist");
				cvo = new CodeVO();
				cvo.setCdNo(rs.getString(1));
				cvo.setCdNm(rs.getString(2));
				cvo.setCdDesc(rs.getString(3));
				cvo.setCdGroupNo(rs.getString(4));
			}

			rs.close();
			ps.close();
			conn.close();
			
			return cvo;
			
		}catch(Exception ex){
			System.out.println("getCodeList exception : " + ex);
			close(conn, ps, rs);
		} 
		return cvo;
	}

}
